package com.example.stugradeapp

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.widget.Toast
import java.time.DateTimeException
import java.util.*

class MyDatabaseHelper (val context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION){


    private val TABLE_NAME="my_library"
    private val COLUMN_ID = "_id"
    private val COLUMN_TITLE = "book_title"
    private val COLUMN_AUTHOR = "book_author"
    private val COLUMN_PAGES = "book_pages"
    private val TABLE_NAME2="User"
    private val query = "create table "+TABLE_NAME+
            " (" +
            COLUMN_ID+" integer primary key autoincrement, " +
            COLUMN_TITLE+" text, " +
            COLUMN_AUTHOR+" text, " +
            COLUMN_PAGES+" integer);"

    private val createUser = "create table "+TABLE_NAME2+" (_id integer primary key autoincrement, name text, pwd text); "

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(createUser)
        db.execSQL(query)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME)
        onCreate(db)
    }
    companion object {
        // If you change the database schema, you must increment the database version.
        const val DATABASE_VERSION = 3
        const val DATABASE_NAME ="BookLibrary.db"
    }

    fun addUser(){
        val db=this.writableDatabase
        val values=ContentValues().apply {
            put("name","201901080204")
            put("pwd","123456")
        }
        db?.insert(TABLE_NAME2,null,values)
    }

    fun readUser():Cursor{
        val query = "select * from User"
        val db = this.writableDatabase
        var cursor: Cursor? =null
        if(db!=null) {
            cursor = db.rawQuery(query, null)
        }
        return cursor as Cursor
    }


    fun addBook(title:String,author:String,pages:Int){
        val db=this.writableDatabase
        val values=ContentValues().apply {
            put(COLUMN_TITLE,title)
            put(COLUMN_AUTHOR,author)
            put(COLUMN_PAGES,pages)
        }
        val newRowId = db?.insert(TABLE_NAME,null,values)
        if(newRowId == (-1).toLong()){
            Toast.makeText(context,"Fail",Toast.LENGTH_SHORT).show()
        }else{
            Toast.makeText(context,"Success",Toast.LENGTH_SHORT).show()
        }
    }

    fun readData():Cursor{
        val query = "select * from " + TABLE_NAME
        val db = this.writableDatabase
        var cursor: Cursor? =null
        if(db!=null) {
            cursor = db.rawQuery(query, null)
        }
        return cursor as Cursor
    }

    fun updateData(id:Int,title: String,author: String,pages: Int){
        val db = this.writableDatabase
        val values=ContentValues().apply {
            put(COLUMN_TITLE,title)
            put(COLUMN_AUTHOR,author)
            put(COLUMN_PAGES,pages)
        }
        //db.update("Book", values, "name = ?", arrayOf("The Da Vinci Code"))
        val newRowId = db?.update(TABLE_NAME,values,"_id = ? ", arrayOf(id.toString()))
//        if(newRowId == (-1)?.toLong()){
//            Toast.makeText(context,"Fail",Toast.LENGTH_SHORT).show()
//        }else{
//            Toast.makeText(context,"Success",Toast.LENGTH_SHORT).show()
//        }
    }

    fun deleteOneRow(id: Int){
        val db = this.writableDatabase
        db?.delete(TABLE_NAME,"_id = ? ", arrayOf(id.toString()))
    }
}